package com.peenongsaban.freelance.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.peenongsaban.freelance.bean.AddressBean;
import com.peenongsaban.freelance.bean.AptitudeBean;
import com.peenongsaban.freelance.bean.AptitudeTypeBean;
import com.peenongsaban.freelance.bean.PaymentOfServiceBean;
import com.peenongsaban.freelance.bean.PaymentTypeBean;
import com.peenongsaban.freelance.bean.PictureBean;
import com.peenongsaban.freelance.bean.PostServiceBean;
import com.peenongsaban.freelance.bean.ServiceBean;
import com.peenongsaban.freelance.bean.UserBean;
import com.peenongsaban.freelance.connection.DatabaseConnectionPool;
import com.peenongsaban.freelance.dao.ListServiceDAO;
import com.peenongsaban.freelance.meta.AddressMeta;
import com.peenongsaban.freelance.meta.AptitudeTypeMeta;
import com.peenongsaban.freelance.meta.PaymentOfServiceMeta;
import com.peenongsaban.freelance.meta.PaymentTypeMeta;
import com.peenongsaban.freelance.meta.PictureMeta;
import com.peenongsaban.freelance.meta.PostServiceMeta;
import com.peenongsaban.freelance.meta.ServiceMeta;
import com.peenongsaban.freelance.meta.UserMeta;
import com.peenongsaban.freelance.meta.ViewMeta;
import com.peenongsaban.freelance.utils.ExceptionUtil;
import com.peenongsaban.freelance.utils.StringUtils;

public class ListServiceDAOImpl implements ListServiceDAO {

	@Override
	public Vector<AptitudeTypeBean> getListAptitudeType() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<AptitudeTypeBean> vector = new Vector<AptitudeTypeBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID
					+ ",J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME + ",J."
					+ AptitudeTypeMeta.COL_IMAGE_ICON + ",L.AMOUNT FROM "
					+ AptitudeTypeMeta.TABLE + " J LEFT JOIN (SELECT "
					+ AptitudeTypeMeta.COL_APTITUDE_TYPE_ID + ","
					+ AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME + ","
					+ "COUNT(*) AS AMOUNT FROM " + ViewMeta.LIST_POST_SERVICE_V
					+ " GROUP BY " + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID
					+ ") L ON J." + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID
					+ " = L." + AptitudeTypeMeta.COL_APTITUDE_TYPE_ID;
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();

			databaseConnection.freeConnection(conn);

			while (rs.next()) {
				AptitudeTypeBean bean = new AptitudeTypeBean();
				bean.setAptitudeTypeID(rs
						.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				bean.setAptitudeTypeName(rs
						.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));
				bean.setImage_icon(rs
						.getString(AptitudeTypeMeta.COL_IMAGE_ICON));
				bean.setCountSumService(rs.getInt("AMOUNT"));
				vector.add(bean);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (statement != null)
					statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public Vector<PostServiceBean> getListServiceByCondition(String sqlCondition) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> postServiceList = new Vector<PostServiceBean>();
		PreparedStatement select_service_statement = null;
		PreparedStatement select_aptitude_statement = null;
		PreparedStatement select_payment_statement = null;
		PreparedStatement select_picture_statement = null;
		try {
			String select_service_sql = "";
			if (!StringUtils.isEmptyOrNull(sqlCondition)) {
				select_service_sql = "SELECT * FROM "
						+ ViewMeta.LIST_POST_SERVICE_V + sqlCondition;
			} else {
				select_service_sql = "SELECT * FROM "
						+ ViewMeta.LIST_POST_SERVICE_V;
			}
			select_service_statement = conn
					.prepareStatement(select_service_sql);
			ResultSet rs_service = select_service_statement.executeQuery();
			while (rs_service.next()) {
				PostServiceBean postService = new PostServiceBean();
				postService.setPostID(rs_service
						.getString(PostServiceMeta.COL_POST_ID));
				postService.setPostDate(rs_service
						.getDate(PostServiceMeta.COL_POST_DATE));
				postService.setAmountRequestPost(rs_service
						.getInt(PostServiceMeta.COL_AMOUNT_REQUEST_POST));

				UserBean user = new UserBean();
				user.setTrustLevel(rs_service
						.getDouble(UserMeta.COL_TRUST_LEVEL));

				AddressBean address = new AddressBean();
				address.setProvince(rs_service
						.getString(AddressMeta.COL_PROVINCE));
				Vector<AddressBean> addressVector = new Vector<AddressBean>();
				addressVector.add(address);
				user.setListAddress(addressVector);
				postService.setUser(user);

				ServiceBean service = new ServiceBean();
				service.setServiceID(rs_service
						.getString(ServiceMeta.COL_SERVICE_ID));
				service.setServiceName(rs_service.getString(ServiceMeta.COL_SERVICE_NAME));
				service.setDescriptionOfService(rs_service
						.getString(ServiceMeta.COL_DESCRIPTION_OF_SERVICE));
				postService.setService(service);

				Vector<AptitudeBean> aptitudeVec = new Vector<AptitudeBean>();
				AptitudeBean aptitude = new AptitudeBean();
				aptitudeVec.add(aptitude);
				postService.setListAptitude(aptitudeVec);
				postService
						.getListAptitude()
						.get(0)
						.getAptitudeType()
						.setAptitudeTypeID(
								rs_service
										.getInt(AptitudeTypeMeta.COL_APTITUDE_TYPE_ID));
				postService
						.getListAptitude()
						.get(0)
						.getAptitudeType()
						.setAptitudeTypeName(
								rs_service
										.getString(AptitudeTypeMeta.COL_APTITUDE_TYPE_NAME));

				Vector<PaymentOfServiceBean> listPaymentOfService = new Vector<PaymentOfServiceBean>();
				String select_payment_sql = "SELECT * FROM "
						+ ViewMeta.PAYMENT_OF_SERVICE_V + " WHERE "
						+ ServiceMeta.COL_SERVICE_ID + " = ?";
				select_payment_statement = conn
						.prepareStatement(select_payment_sql);
				select_payment_statement.setString(1, service.getServiceID());
				ResultSet rs_payment = select_payment_statement.executeQuery();
				while (rs_payment.next()) {
					PaymentOfServiceBean payment = new PaymentOfServiceBean();
					payment.setPaymentId(rs_payment
							.getString(PaymentOfServiceMeta.COL_PAYMENT_ID));
					payment.setPrice(rs_payment
							.getDouble(PaymentOfServiceMeta.COL_PRICE));
					payment.setDescription(rs_payment
							.getString(PaymentOfServiceMeta.COL_DESCRIPTION));

					PaymentTypeBean paymentType = new PaymentTypeBean();
					paymentType.setPaymentTypeID(rs_payment
							.getInt(PaymentTypeMeta.COL_PAYMENT_TYPE_ID));
					paymentType.setPaymentTypeName(rs_payment
							.getString(PaymentTypeMeta.COL_PAYMENT_TYPE_NAME));

					payment.setPaymentType(paymentType);

					Vector<PictureBean> listPicture = new Vector<PictureBean>();
					String select_picture_sql = "SELECT * FROM "
							+ ViewMeta.PICTURE_OF_SERVICE_V + " WHERE "
							+ PaymentOfServiceMeta.COL_PAYMENT_ID + " = '?";
					select_picture_statement = conn
							.prepareStatement(select_picture_sql);
					select_picture_statement.setString(1, payment.getPaymentId());
					ResultSet rs_picture = select_picture_statement
							.executeQuery();
					while (rs_picture.next()) {
						PictureBean picture = new PictureBean();
						picture.setPictureLocation(rs_picture
								.getString(PictureMeta.COL_PICTURE_LOCATION));
						listPicture.add(picture);
					}

					payment.setListPicture(listPicture);

					listPaymentOfService.add(payment);
				}
				service.setListPaymentOfService(listPaymentOfService);
				postService.setService(service);

				postServiceList.add(postService);
			}

			databaseConnection.freeConnection(conn);

		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (select_service_statement != null)
					select_service_statement.close();
				if (select_aptitude_statement != null)
					select_aptitude_statement.close();
				if (select_payment_statement != null)
					select_payment_statement.close();
				if (select_picture_statement != null)
					select_picture_statement.close();

				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return postServiceList;
	}

	@Override
	public Vector<PostServiceBean> getListServiceLastUpdate() {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		Vector<PostServiceBean> vector = new Vector<PostServiceBean>();
		PreparedStatement statement = null;
		try {
			String sql = "SELECT " + PostServiceMeta.COL_POST_ID + ","
					+ ServiceMeta.COL_SERVICE_NAME + " FROM "
					+ ViewMeta.LIST_POST_SERVICE_V + " ORDER BY "
					+ PostServiceMeta.COL_POST_DATE + " DESC LIMIT 0,9";
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();

			databaseConnection.freeConnection(conn);

			while (rs.next()) {
				PostServiceBean post = new PostServiceBean();
				post.setPostID(rs.getString(PostServiceMeta.COL_POST_ID));

				ServiceBean service = new ServiceBean();
				service.setServiceName(rs.getString(ServiceMeta.COL_SERVICE_NAME));

				
				post.setService(service);
				vector.add(post);
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				if (statement != null)
					statement.close();

				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return vector;
	}

	@Override
	public int countTotalJob(String sqlCondition) {
		DatabaseConnectionPool databaseConnection = new DatabaseConnectionPool();
		Connection conn = databaseConnection.getConnection();
		PreparedStatement statement = null;
		int size = 0;
		try {
			String sql = "";
			if (!StringUtils.isEmptyOrNull(sqlCondition)) {
				sql = "SELECT COUNT(*) AS CNT FROM "
						+ ViewMeta.LIST_POST_SERVICE_V + sqlCondition;
			} else {
				sql = "SELECT COUNT(*) AS CNT FROM "
						+ ViewMeta.LIST_POST_SERVICE_V;
			}
			statement = conn.prepareStatement(sql);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				size = rs.getInt("CNT");
			}
		} catch (SQLException e) {
			ExceptionUtil.messageException(new Throwable(), e);
		} finally {
			try {
				statement.close();
				databaseConnection.destroy();
				databaseConnection = null;
				conn.close();
			} catch (Exception ex) {
				ExceptionUtil.messageException(new Throwable(), ex);
			}
		}
		return size;
	}
}
